![]() |
![]() |
![]() |
![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() ![]() |
![]() |
![]() |
To access the contents, click the chapter and section titles.
Oracle Performance Tuning and Optimization
Access MethodsThe following hints provide the optimizer with the specific access method you think is more appropriate, based on information you know about your data and your application. These hints are valid only if the referenced index or cluster is available and the syntax of the SQL statement is correct. If the syntax is incorrect or the referenced index or cluster is not available, the hint is ignored. When specifying an access path for an SQL statement, the table to be accessed must be specified exactly. If the table is accessed with an alias, you must specify the alias. AND_EQUAL The AND_EQUAL hint specifies an execution plan that uses an access path that merges the scans of several single-column indexes. The syntax of this hint is as follows: /*+ AND_EQUAL( table index index [ index .... index ] ) */ CLUSTER The CLUSTER hint specifies that the optimizer should choose a cluster scan to access the specified table. The syntax of this hint is as follows: /*+ CLUSTER( table ) */ FULL The FULL hint specifies that the optimizer should choose a full-table scan to access the specified table. The syntax of this hint is as follows: /*+ FULL( table ) */ This hint specifies the use of a full-table scan, even though there may be an index present on this table and the index key is specified in the WHERE clause.
HASH The HASH hint specifies that the optimizer should choose a hash scan to access the specified table. The syntax of this hint is as follows: /*+ HASH( table ) */ INDEX The INDEX hint specifies that the optimizer should choose an index scan to access the specified table. The syntax of this hint is as follows: /*+ INDEX( table index [ index .. index ] ) */ This hint may contain one or more indexes. The following choices may be made by the optimizer:
The INDEX hint specifies the use of an index scan, even though the optimizer may not choose an index scan because of a low number of distinct values. If you know that the value in the WHERE clause is somewhat unique, you can improve performance by using this hint.
INDEX_ASC The INDEX_ASC hint specifies that the optimizer should choose an index scan to access the specified table. The syntax of this hint is as follows: /*+ INDEX_ASC( table index [ index .. index ] ) */ This hint can contain one or more indexes. The INDEX_ASC hint is similar to the INDEX hint except that if the statement specifies an index range scan, the entries are scanned in ascending order of their indexed values.
|
![]() |
Products | Contact Us | About Us | Privacy | Ad Info | Home
Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. |